import tempfile
import frappe
from frappe.model.document import Document
from frappe import _
from frappe.utils import strip_html
from frappe.translate import print_language
import os, json, re, openpyxl
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, Protection # Assuming Protection is also used
from io import BytesIO
from copy import copy
from bs4 import BeautifulSoup
import subprocess
import platform
import gc
import psutil
import resource
import time

# 安装依赖库（在第一次运行时自动安装）
try:
    import psutil
except ImportError:
    frappe.msgprint(_("Installing required dependencies..."))
    subprocess.run(['pip', 'install', 'psutil', 'Pillow'], check=True)
    import psutil
    from PIL import Image as PILImage

logged_error = set()    # 避免记录重复错误，超量记录错误

# 替换系统的strip_html函数, 保留换行功能
def html_to_plain_text(html_content):
    # 使用BeautifulSoup提取纯文本
    soup = BeautifulSoup(html_content, "html.parser")
    plain_text = soup.get_text(separator="\n")  # 保留换行符
    return plain_text.strip()

# 内存使用监控函数
def log_memory_usage(context):
    """记录内存使用情况"""
    try:
        process = psutil.Process(os.getpid())
        mem_info = process.memory_info()
        frappe.logger().info(
            f"Memory usage at {context}: "
            f"RSS={mem_info.rss/1024/1024:.2f}MB, "
            f"VMS={mem_info.vms/1024/1024:.2f}MB"
        )
    except Exception as e:
        frappe.log_error(f"Memory logging failed: {str(e)}", "Memory Monitor")

def is_memory_critical(threshold=0.85):
    """检查内存使用是否接近临界值"""
    try:
        mem = psutil.virtual_memory()
        return mem.percent > threshold * 100
    except Exception:
        return False

def compress_image(original_path, max_size=2000, quality=70):
    """压缩图片以减少内存占用"""
    try:
        from PIL import Image as PILImage
        
        # 创建临时文件
        temp_dir = frappe.get_site_path("private", "temp_images")
        os.makedirs(temp_dir, exist_ok=True)
        compressed_path = os.path.join(temp_dir, f"compressed_{int(time.time())}_{os.path.basename(original_path)}")
        
        # 打开并压缩图片
        with PILImage.open(original_path) as img:
            if img.mode != 'RGB':
                img = img.convert('RGB')
                
            # 保持原始宽高比
            img.thumbnail((max_size, max_size), PILImage.LANCZOS)
            
            # 保存压缩后的图片
            img.save(compressed_path, quality=quality, optimize=True)
            
        return compressed_path
    except Exception as e:
        frappe.log_error(f"Image compression failed: {str(e)}", "Image Compression")
        return original_path  # 压缩失败时返回原路径

@frappe.whitelist()
def export_data(doc, template_file, template_name='', export_file_name='', language=None, async_export=False):
    """
    导出数据到Excel或Word模板
    """
    try:
        # 1. 解析数据
        doc_data = doc
        if isinstance(doc, str):
            doc_data = json.loads(doc)
            
        # 2. 根据数据结构决定是否转换为 Doc 对象
        # 列表视图的模拟 Doc 只有 'items' 键，没有 'doctype' 键
        if isinstance(doc_data, dict) and doc_data.get('doctype'):
            # 这是来自单据页面的数据，将其转换为 Frappe Doc 对象以支持 run_method
            doc_obj = frappe.get_doc(doc_data)
        elif isinstance(doc_data, dict) and (doc_data.get('items') is not None or
            doc_data.get('data') is not None
        ):
             # 这是来自列表视图的模拟 Doc，直接使用字典
            doc_obj = doc_data 
        else:
             # 如果既没有 doctype 也没有 items，可能是格式错误
            frappe.throw(_("Invalid data format for export."), frappe.ValidationError)

        # 3. 提前执行 before_print 验证 (仅对 Doc 对象执行)
        if isinstance(doc_obj, frappe.model.document.Document):
            print_settings = frappe.get_single("Print Settings").as_dict()
            try:
                doc_obj.run_method("before_print", print_settings)
            except frappe.ValidationError as ve:
                return {"success": False, "message": str(ve), "validation_error": True}
        
        # 4. 异步导出处理
        if async_export:
            # 异步处理需要传递可序列化的数据，而不是 Doc 对象
            frappe.enqueue(
                "_execute_export_data",
                queue="long",
                timeout=600,
                # 传递原始的字典数据或序列化的Doc对象
                doc=doc_data, 
                template_file=template_file,
                template_name=template_name,
                export_file_name=export_file_name,
                language=language
            )
            return {
                "status": "queued", 
                "message": _("Export started in background. You will be notified when it's ready.")
            }
        else:
            # 同步处理，直接使用转换后的 Doc 对象或字典
            return _execute_export_data(doc_obj, template_file, template_name, export_file_name, language)
            
    except Exception as e: 
        # ... 错误处理逻辑保持不变 ...
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("Export data error", traceback)
        return {
            "success": False, 
            "message": _("Export failed due to system error. Please contact administrator.")
        }

def _execute_export_data(doc, template_file, template_name, export_file_name, language):
    """
    实际执行导出操作的内部函数
    
    这里的 doc 可能是 Frappe Doc 对象（来自单据）或 Python 字典（来自列表视图）。
    """
    try:
        log_memory_usage("Start export_data")
        
        # 获取模板文件路径
        bench_path = frappe.utils.get_bench_path()
        template_file = template_file if template_file[:9] == '/private/' else f"/public{template_file}"
        path = f"{bench_path}/sites/{frappe.local.site}{template_file}"
        
        log_memory_usage(f"Before loading template: {path}")
        
        # 确定 doc 的 'name' 字段。如果 doc 是 Doc 对象，用 .name；如果是字典，用 .get('name')
        doc_name = doc.name if isinstance(doc, frappe.model.document.Document) else doc.get("name", "unknown")
        
        # 处理文件名模板
        default_filename = f'{template_name}_{doc_name}.{path[-4:]}'
        filename = default_filename
        
        if export_file_name:
            try:
                # Frappe 渲染模板需要传入字典形式的 doc
                doc_to_render = doc.as_dict() if isinstance(doc, frappe.model.document.Document) else doc
                
                filename = frappe.render_template(
                    export_file_name, 
                    {
                        'doc': doc_to_render, 
                        'template_name': template_name, 
                        'path': path
                    }
                )
                # 验证文件名是否有效
                if not filename or not isinstance(filename, str):
                    filename = default_filename
            except Exception as e: 
                error_msg = _("Export file name template error: {0}. Using default filename.").format(str(e))
                frappe.log_error("Filename template error", error_msg)
                filename = default_filename
        
        # 根据文件类型处理模板
        if path.lower().endswith('.docx'):
            temp_file = process_docxtpl(path, doc) # 确保 process_docxtpl 能处理字典或 Doc 对象
        else:
            temp_file = openpyxl.load_workbook(filename=path)
            for ws in temp_file.worksheets:
                # 确保 process_worksheet 能处理字典或 Doc 对象
                process_worksheet(ws, doc, language, bench_path) 
                apply_vertical_merges(ws)
        
        log_memory_usage("After processing template")
                
        in_memory_file = BytesIO()
        temp_file.save(in_memory_file)
        file_bytes = in_memory_file.getvalue()
        
        log_memory_usage("After saving to memory")
        
        return {
            "success": True,
            "file_content": file_bytes.hex(), 
            "file_name": filename,
            "content_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" if path.lower().endswith(('.xlsx', '.xls')) else "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
        }
        
    except Exception as e:
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("Export template processing error", traceback)
        return {
            "success": False, 
            "message": _("Export failed due to template processing error. Please check the error log for details.")
        }
 
def docx_to_pdf(docx_bytes):
    from docx import Document
    from html2pdf import converter

    try:
        doc = Document(docx_bytes)
        # Convert docx to HTML
        html = doc.styles.default_font.element.xml
        for paragraph in doc.paragraphs:
            html += paragraph.element.xml
        for table in doc.tables:
            html += table.element.xml

        # Create a temporary HTML file
        with tempfile.NamedTemporaryFile(suffix='.html', delete=False) as html_file:
            tmp_html_path = html_file.name 
            html_file.write(html.encode('utf-8'))

        # Convert HTML to PDF
        with tempfile.NamedTemporaryFile(suffix='.pdf', delete=False) as pdf_file:
            tmp_pdf_path = pdf_file.name 
            converter.convert(html_file.name, pdf_file.name)

        with open(tmp_pdf_path, "rb") as f:
            pdf_bytes = f.read()
    except (IOError, docx.DocumentError):
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)        
    finally:
        if os.path.exists(tmp_html_path):
            os.remove(tmp_html_path)  
        if os.path.exists(tmp_pdf_path):
            os.remove(tmp_pdf_path)  

    return pdf_bytes

def process_docxtpl(template_file_name, doc, language=None, bench_path=None):
    from docxtpl import DocxTemplate

    tpl = DocxTemplate(template_file_name)
    #模板中支持{{字段名}} 或 {{doc.字段名}} 访问字段变量值
    context = doc.as_dict()
    context['doc'] = doc
    tpl.render(context, frappe.get_jenv())

    return tpl

def process_worksheet(ws, doc, language=None, bench_path=None):
    log_memory_usage("Start process_worksheet")
    
    # 限制处理范围
    max_row, max_column = find_max_data_row_and_column(ws)
    max_row = min(max_row, 200)  # 绝对最大行数
    max_column = min(max_column, 50)  # 绝对最大列数
    
    # 分批处理数据
    batch_size = 50
    for batch_start in range(1, max_row + 1, batch_size):
        batch_end = min(batch_start + batch_size - 1, max_row)
        # 修复：传递当前批次结束行，允许函数内部修改
        batch_end = _process_worksheet_batch(ws, doc, language, bench_path, batch_start, batch_end, max_column)
        
        # 每处理一个批次后检查内存
        if is_memory_critical():
            frappe.logger().warning("Memory critical during processing, forcing GC")
            gc.collect()
            
    log_memory_usage("End process_worksheet")

def _get_row_comments(ws, row_idx, max_column):
    """Pre-fetches and returns comments for all cells in a given row."""
    row_comments = {}
    for j_idx in range(1, max_column + 1):
        cell_at_j_idx = ws.cell(row=row_idx, column=j_idx)
        if cell_at_j_idx.comment:
            row_comments[j_idx] = cell_at_j_idx.comment.text.strip().lower()
    return row_comments

def _cache_and_clear_style_conditions(ws, template_row_idx, max_column, current_row_comments, logged_error):
    """
    Scans the template_row_idx for style_cond comments, caches their rules,
    and clears the comments from the template cells to prevent copying.
    Returns the dictionary of cached style conditions.
    """
    cached_style_conditions = {}
    for k in range(1, max_column + 1):
        template_cell_k = ws.cell(row=template_row_idx, column=k)
        temp_comment = template_cell_k.comment
        temp_comment_text_stripped_lower_k = temp_comment.text.strip().lower() if temp_comment else ""

        if temp_comment_text_stripped_lower_k.startswith("style_cond:"):
            try:
                parts = temp_comment_text_stripped_lower_k.split(':', 2)
                if len(parts) == 3:
                    condition_str = parts[1].strip()
                    styles_str = parts[2].strip()
                    cached_style_conditions[k] = (condition_str, styles_str)
                    # IMPORTANT: Clear the comment from the template cell immediately.
                    # This prevents insert_rows_with_copy from copying it.
                    template_cell_k.comment = None
                    # Also clear from the current_row_comments cache if it was there
                    if k in current_row_comments:
                        current_row_comments[k] = ""
            except Exception as e:
                frappe.log_error(
                    f"Error caching style_cond comment: {temp_comment_text_stripped_lower_k} - {str(e)}",
                    "Excel Export Style"
                )
                template_cell_k.comment = None # Clear even on error
                if k in current_row_comments:
                    current_row_comments[k] = "" # Clear from cache
    return cached_style_conditions

def _handle_row_directive(ws, current_row_idx, max_column, doc, current_sheet_end_row, current_row_comments, logged_error):
    """
    Handles the 'row=' directive: evaluates the expression, caches style conditions,
    inserts new rows, and updates the sheet's effective end row.
    Returns (list of row data, updated sheet end row, cached style conditions for the block).
    """
    cell_a1 = ws.cell(row=current_row_idx, column=1)
    row_expression = cell_a1.comment.text.strip()[4:] # Use original cell.comment for its text here

    rows_data = []
    num_rows_to_insert = 0
    cached_style_conditions = {}

    try:
        rows_data = frappe.safe_eval(row_expression, None, {"doc": doc})

        # Cache style_cond comments from this template row and clear them
        cached_style_conditions = _cache_and_clear_style_conditions(
            ws, current_row_idx, max_column, current_row_comments, logged_error
        )

        cell_a1.comment = None # Clear 'row=' comment from this A column cell
        current_row_comments[1] = "" # Also clear from current_row_comments cache

        rows_count = len(rows_data)
        if rows_count > 1:
            if rows_count > 1500: # Max rows limit
                frappe.throw(_("Too many rows to insert (max 1500 allowed)"))

            num_rows_to_insert = rows_count - 1
            # insert_rows_with_copy should now copy cells WITHOUT the style_cond comments
            insert_rows_with_copy(ws, current_row_idx + 1, num_rows_to_insert)
            current_sheet_end_row += num_rows_to_insert

            # This loop clears comments from column 1 of newly inserted rows (for copied 'row=' comments)
            for r_idx_new in range(current_row_idx + 1, current_row_idx + 1 + num_rows_to_insert):
                new_cell = ws.cell(row=r_idx_new, column=1)
                if new_cell.comment:
                    new_cell.comment = None

    except Exception as e:
        frappe.log_error(f"Error evaluating row expression: {row_expression}", "Excel Export")
        rows_data = [] # Ensure rows_data is empty if eval fails
        cached_style_conditions = {} # Reset cache on error

    return rows_data, current_sheet_end_row, cached_style_conditions

def _render_cell_content(ws, cell, text, doc, current_row_data, bench_path, logged_error, pre_row_cell_value=None):
    """
    Renders template strings in a cell, handles image paths, and converts HTML.
    Updates cell.value directly.
    """

    
    if not text or not has_variable_string(text):
        return # No variable to render

    try:
        value = frappe.render_template(text, {'doc': doc, 'row': current_row_data})
        if value and isinstance(value, str):
            if value.startswith("/") and value.lower().endswith(('.jpg', '.png', '.jpeg')):
                # same image, skip add_image in the following rows
                #frappe.log_error('excel export debug prev row', f"text={text}, {pre_row_cell_value}, {value}")            
                if pre_row_cell_value and pre_row_cell_value == value:
                    cell.value = '' # Clear cell value if image added
                    return value

                file_path = value if value.startswith('/private/') else f'/public{value}'
                abs_path = f"{bench_path}/sites/{frappe.local.site}{file_path}"

                if not os.path.exists(abs_path):
                    frappe.logger().warning(f"Image file not found: {abs_path}")
                    cell.value = "[Image Missing]"
                else:
                    add_image(ws, cell.row, cell.column, abs_path)
                    cell.value = '' # Clear cell value if image added
            #支持动态格式化, 支持description这样的文本编辑器字段，内部是html格式存储的        
            elif strip_html(value).startswith("formatter:"):
                parse_formatter_instructions(ws, cell, strip_html(value))
            else:
                value = html_to_plain_text(value or '').replace('None', '')
                cell.value = text if "{{ no such element:" in value else value
        else:
            cell.value = ""
        
        return value
    except Exception as e:
        if text not in logged_error and len(logged_error) < 10:
            logged_error.add(text)
            traceback = frappe.get_traceback(with_context=True)
            frappe.log_error("export excel render template error", traceback)
            cell.value = f"[Error: {str(e)}]"

def _apply_style_condition(cell, condition_str, styles_str, logged_error):
    """
    Evaluates a style condition (e.g., len>) and applies specified font styles to a cell.
    """
    apply_style = False
    if condition_str.startswith("len>"):
        try:
            threshold = int(condition_str[4:])
            if len(str(cell.value or '')) > threshold:
                apply_style = True
        except ValueError:
            frappe.log_error(f"Invalid length threshold in style condition: {condition_str}", "Excel Export Style")
            return # Don't apply style on malformed condition

    if apply_style:
        # Create a copy of the existing font or a new Font object if none exists
        current_font = copy(cell.font) if cell.font else Font()

        styles_list = [s.strip() for s in styles_str.split(',')]
        for style_item in styles_list:
            if style_item == "bold":
                current_font.bold = True
            elif style_item.startswith("size="):
                try:
                    current_font.size = int(style_item[5:])
                except ValueError:
                    frappe.log_error(f"Invalid font size in style: {style_item}", "Excel Export Style")
        cell.font = current_font
    

def _process_worksheet_batch(ws, doc, language, bench_path, start_row, end_row, max_column):
    """
    Processes a batch of rows in a worksheet, handling template rendering,
    row expansion via 'row=' comments, and conditional styling.
    """
    # Variables to manage repeating data blocks
    current_block_rows_data = [] # List of data rows for the current 'row=' block
    current_block_data_index = -1 # Index of the current data row being processed within current_block_rows_data
    current_evaluated_row_data = frappe._dict() # The actual data dict for the current row (from doc or block data)

    # Tracks the effective last row on the sheet, which can grow due to row expansion
    current_sheet_end_row = end_row

    # Dictionary to store style_cond rules, populated when a 'row=' instruction is found
    active_template_row_style_conditions = {}

    # Set to store errors once to avoid repeated logging for the same error in template
    logged_error = set()

    with print_language(language):
        row_idx = start_row
        last_row_value = {} #track the last row value, in order to prevent output same image the following row
        while row_idx <= current_sheet_end_row:
            # Memory usage logging and garbage collection
            if row_idx % 10 == 0:
                log_memory_usage(f"Processing row {row_idx}")
                if is_memory_critical():
                    frappe.logger().warning("Memory critical during processing, forcing GC")
                    gc.collect()

            # Pre-fetch comments for the current physical row on the sheet
            # This is an optimization for the current row's processing.
            current_physical_row_comments = _get_row_comments(ws, row_idx, max_column)

            # Flag to indicate if the current row (row_idx) triggered a new 'row=' block expansion.
            # This is important for resetting block variables *after* all iterations of the current block.
            is_new_block_start = False

            # Iterate through columns of the current row            
            for col_idx in range(1, max_column + 1):
                cell = ws.cell(row=row_idx, column=col_idx)
                comment_text_lower = current_physical_row_comments.get(col_idx, "")

                # 1. Handle 'row=' comment (always in column A, highest priority)
                if col_idx == 1 and comment_text_lower.startswith("row="):
                    is_new_block_start = True
                    current_block_rows_data, current_sheet_end_row, active_template_row_style_conditions = \
                        _handle_row_directive(ws, row_idx, max_column, doc, current_sheet_end_row, current_physical_row_comments, logged_error)
                    current_block_data_index = -1 # Reset data index for the new block

                # 2. Update context for template rendering (`row` variable)
                # This only happens for the first column (A) as it defines the row's data context.
                if col_idx == 1:
                    if current_block_rows_data and (current_block_data_index + 1) < len(current_block_rows_data):
                        current_block_data_index += 1
                        current_evaluated_row_data = current_block_rows_data[current_block_data_index]
                    else:
                        # If not inside a repeating block, or block finished, use the main doc
                        current_evaluated_row_data = frappe._dict() # Reset if block finished
                        # The original code implicitly used `doc` when `row` was empty.
                        # For now, we rely on `frappe.render_template` handling `doc` when `row` is empty.

                # 3. Render cell values

                last_row_value[col_idx] = _render_cell_content(ws, cell, str(cell.value or ''), doc, current_evaluated_row_data, bench_path, 
                    logged_error, pre_row_cell_value=last_row_value.get(col_idx)
                )

                # 4. Apply conditional styles
                if active_template_row_style_conditions and col_idx in active_template_row_style_conditions:
                    condition_str, styles_str = active_template_row_style_conditions[col_idx]
                    try:
                        _apply_style_condition(cell, condition_str, styles_str, logged_error)
                    except Exception as e:
                        frappe.log_error(f"Error applying cached style_cond: {condition_str}, {styles_str} - {str(e)}", "Excel Export Style")

            # 5. Reset block-related variables if the current repeating block has completed all its iterations.
            # This check ensures we don't reset when we just *started* a new block.
            if not is_new_block_start and current_block_rows_data and (current_block_data_index + 1) >= len(current_block_rows_data):
                current_block_rows_data = []
                current_block_data_index = -1
                current_evaluated_row_data = frappe._dict() # Clear data context
                active_template_row_style_conditions = {} # Reset for future blocks

            row_idx += 1
    return current_sheet_end_row

def add_image(ws, row, column, path, width=0, height=0, scale=0.9):
    try:
        # Check file size and compress if larger than 1MB
        if os.path.exists(path) and os.path.getsize(path) > 1024 * 1024:  # 1MB
            compressed_path = compress_image(path)
            if compressed_path:
                path = compressed_path
                frappe.logger().info(f"Using compressed image: {path}")

        # Load the image
        img = Image(path)

        # Check if the cell is part of a merged cell range
        merged_range = None
        for merged_cell in ws.merged_cells.ranges:
            if merged_cell.min_row <= row <= merged_cell.max_row and merged_cell.min_col <= column <= merged_cell.max_col:
                merged_range = merged_cell
                break

        # Define conversion factors
        COL_PX_FACTOR = 64 / 8.43  # ≈7.59, based on default column width 8.43 units = 64 pixels
        ROW_PX_FACTOR = 96 / 72    # ≈1.333, assuming 96 DPI
        PADDING_PX = 2  # Small padding to account for borders/gridlines

        if merged_range:
            # Calculate total width and height of the merged cell range in pixels
            total_width_px = 0
            total_height_px = 0

            # Sum the widths of all columns in the merged range
            for col_idx in range(merged_range.min_col, merged_range.max_col + 1):
                col_letter = get_column_letter(col_idx)
                col_width = ws.column_dimensions[col_letter].width
                col_width = col_width if col_width else 8.43  # Default
                total_width_px += col_width * COL_PX_FACTOR

            # Sum the heights of all rows in the merged range
            for row_idx in range(merged_range.min_row, merged_range.max_row + 1):
                row_height = ws.row_dimensions[row_idx].height
                row_height = row_height if row_height else 15  # Default
                total_height_px += row_height * ROW_PX_FACTOR

            # Apply padding
            total_width_px = max(total_width_px - PADDING_PX * 2, 0)
            total_height_px = max(total_height_px - PADDING_PX * 2, 0)

        else:
            # Single cell case
            column_letter = get_column_letter(column)
            col_width = ws.column_dimensions[column_letter].width
            row_height = ws.row_dimensions[row].height
            col_width = col_width if col_width else 8.43
            row_height = row_height if row_height else 15
            total_width_px = col_width * COL_PX_FACTOR - PADDING_PX * 2
            total_height_px = row_height * ROW_PX_FACTOR - PADDING_PX * 2

        # If width and height are explicitly provided, use them
        if width and height:
            img.width = width
            img.height = height
        else:
            # Calculate scaling to fit image within the cell or merged cell range
            img_width_px = img.width
            img_height_px = img.height
            width_ratio = total_width_px / img_width_px
            height_ratio = total_height_px / img_height_px
            resize_ratio = min(width_ratio, height_ratio) * scale  # Apply user-defined scale factor

            # Apply the scaling
            img.width = img_width_px * resize_ratio
            img.height = img_height_px * resize_ratio

        # Log dimensions for debugging
        frappe.logger().info(
            f"Image at {get_column_letter(column)}{row}: "
            f"Merged range={merged_range}, "
            f"Cell dimensions={total_width_px:.2f}x{total_height_px:.2f}px, "
            f"Original image={img_width_px}x{img_height_px}px, "
            f"Scaled image={img.width:.2f}x{img.height:.2f}px"
        )

        # Set the image anchor to the top-left corner of the cell or merged cell
        anchor_cell = f"{get_column_letter(column)}{row}"
        if merged_range:
            anchor_cell = f"{get_column_letter(merged_range.min_col)}{merged_range.min_row}"
        img.anchor = anchor_cell

        # Add the image to the worksheet
        ws.add_image(img)

    except Exception as e:
        if path not in logged_error and len(logged_error) < 10:
            logged_error.add(path)
            traceback = frappe.get_traceback(with_context=True)
            frappe.log_error(f"Export excel add image error: {str(e)}", traceback)
        ws.cell(row=row, column=column).value = f"[Image Error: {os.path.basename(path)}]"

compiled_pattern = re.compile(r'\{\{(?=.*?\}\})')

def has_variable_string(s):
    """at least one occurance of {{变量名}}"""

    if isinstance(s, str):
        return bool(compiled_pattern.search(s))  

def copy_style(source_cell, target_cell):
    """完整复制单元格样式"""
    if source_cell.font:
        target_cell.font = copy(source_cell.font)
    if source_cell.border:
        target_cell.border = copy(source_cell.border)
    if source_cell.fill:
        target_cell.fill = copy(source_cell.fill)
    if source_cell.alignment:
        target_cell.alignment = copy(source_cell.alignment)
    target_cell.number_format = source_cell.number_format

def insert_rows_with_copy(ws, row_index, num_rows_to_insert):
    """
    插入多行并复制内容和样式，包括行高。
    修正了 openpyxl 在 insert_rows 后行高维度未移动的问题，通过手动清除和恢复来解决。
    """
    # 模板行是插入位置的前一行
    template_row = row_index - 1
    
    # 1. 获取模板行高 (用于新插入的行)
    template_row_dim = ws.row_dimensions.get(template_row)
    template_height = 15.0 # openpyxl 的默认行高
    
    if template_row_dim and template_row_dim.height is not None:
        template_height = template_row_dim.height

    # 2. 备份被移动行（'合计'行及以下）的行高
    # 这里的 r_idx 是原始行号
    shifted_row_heights = {}
    
    # 记录需要清除的行号（即插入点及之后的行号）
    rows_to_clear = []
    
    for r_idx, dim in ws.row_dimensions.items():
        # 只需要备份插入点及之后的行
        if r_idx >= row_index and dim.height is not None:
            shifted_row_heights[r_idx] = dim.height
            rows_to_clear.append(r_idx)
    
    # 3. 插入新行
    ws.insert_rows(row_index, num_rows_to_insert)
    
    # *** 关键修正步骤 3.5：清除未移动的行高维度 ***
    # 必须手动清除这些行号，因为 ws.insert_rows 移动了数据，但行高维度还留在原地。
    # 如果不清除，后续恢复操作会与留在原位的行高维度冲突。
    for r_idx in rows_to_clear:
        if r_idx in ws.row_dimensions:
            del ws.row_dimensions[r_idx]

    # 处理插入点以下的合并单元格 (这部分逻辑是正确的)
    for merged_cell in list(ws.merged_cells.ranges):
        if merged_cell.min_row >= row_index:
            merged_cell.shift(0, num_rows_to_insert)

    # 4. 复制模板行的样式到所有新插入的行 (确保新明细行行高正确)
    max_col = min(50, ws.max_column)
    
    for i in range(num_rows_to_insert):
        target_row = row_index + i
        
        # 强制设置行高为模板行的行高
        if target_row not in ws.row_dimensions:
            # 必须使用 openpyxl.worksheet.dimensions.RowDimension
            ws.row_dimensions[target_row] = openpyxl.worksheet.dimensions.RowDimension(ws, index=target_row)
        
        ws.row_dimensions[target_row].height = template_height

        # 复制单元格内容和样式 (保持不变)
        for col_idx in range(1, max_col + 1):
            src_cell = ws.cell(row=template_row, column=col_idx)
            dest_cell = ws.cell(row=target_row, column=col_idx)
            
            dest_cell.value = src_cell.value
            copy_style(src_cell, dest_cell) 

        copy_merged_cells(ws, template_row, target_row) 

    # 5. 恢复被移动行（'合计'行及以下）的行高 - 关键修正
    
    # 现在应用正确的全量偏移：num_rows_to_insert
    for original_r_idx, height in shifted_row_heights.items():
        # 计算正确的新索引：数据移动了多少，行高就应该移动多少
        new_r_idx = original_r_idx + num_rows_to_insert 
        
        # 确保目标行有行维度对象
        if new_r_idx not in ws.row_dimensions:
            ws.row_dimensions[new_r_idx] = openpyxl.worksheet.dimensions.RowDimension(ws, index=new_r_idx)
        
        # 恢复行高
        ws.row_dimensions[new_r_idx].height = height
    
    return

def clear_merged_cells_on_row(worksheet, row_number):
    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_row, max_row = merged_cell.min_row, merged_cell.max_row
        if min_row == row_number:
            worksheet.unmerge_cells(str(merged_cell))

def copy_merged_cells(worksheet, source_row, target_row):
    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_col, max_col, min_row, max_row = merged_cell.min_col, merged_cell.max_col, merged_cell.min_row, merged_cell.max_row
        if min_row == source_row and max_row == source_row:
            new_merged_cell = f"{get_column_letter(min_col)}{target_row}:{get_column_letter(max_col)}{target_row}"
            worksheet.merge_cells(new_merged_cell)

def find_max_data_row_and_column(ws):
    """最多1000行，100列，优化性能"""

    max_row = 0  
    max_column = 0  
    
    # 限制检查范围
    for row in ws.iter_rows(min_row=1, max_col=50, max_row=200):  
        for cell in row:  
            if cell.value and (isinstance(cell.value, str) and cell.value.strip() != '' or not isinstance(cell.value, str)):                  
                max_row = max(max_row, cell.row)  
                max_column = max(max_column, cell.column)
                
    # 安全限制
    max_row = min(max_row, 200)
    max_column = min(max_column, 50)
    
    return max_row, max_column

@frappe.whitelist()
def get_export_excel_template(doctype=None, enable_list_view=0):
    filters = {'template_file': ['is','set']}
    if doctype:
        filters['reference_doctype'] = doctype
    if enable_list_view:
        filters["enable_list_view"] = 1
    return frappe.get_all("Excel Export Template",
        filters = filters,
        fields = ['template_name','template_file', 'export_file_name', 'condition',
            'is_multi_language', 'reference_doctype', 'enable_export_excel', 'enable_export_pdf']
    )

@frappe.whitelist()
def export_pdf(doc, template_file, template_name='', export_file_name='', language=None, async_export=False):
    """
    导出数据到PDF
    """
    try:
        # 先进行验证，不通过直接返回错误
        if isinstance(doc, str):
            doc = frappe.get_doc(json.loads(doc))
                
        # 提前执行 before_print 验证
        print_settings = frappe.get_single("Print Settings").as_dict()
        try:
            doc.run_method("before_print", print_settings)
        except frappe.ValidationError as ve:
            return {"success": False, "message": str(ve), "validation_error": True}
        
        # 异步导出处理
        if async_export:
            frappe.enqueue(
                "_execute_export_pdf",
                queue="long",
                timeout=600,
                doc=doc,
                template_file=template_file,
                template_name=template_name,
                export_file_name=export_file_name,
                language=language
            )
            return {
                "status": "queued", 
                "message": _("PDF export started in background. You will be notified when it's ready.")
            }
        else:
            # 同步处理
            return _execute_export_pdf(doc, template_file, template_name, export_file_name, language)
            
    except Exception as e:        
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("Export PDF error", traceback)
        return {
            "success": False, 
            "message": _("PDF export failed due to system error. Please contact administrator.")
        }

def _execute_export_pdf(doc, template_file, template_name, export_file_name, language):
    """实际执行PDF导出的内部函数"""
    try:
        # 检查LibreOffice是否安装
        if not is_libreoffice_installed():
            install_libreoffice()
        
        # 获取模板文件路径
        bench_path = frappe.utils.get_bench_path()
        template_file = template_file if template_file[:9] == '/private/' else f"/public{template_file}"
        path = f"{bench_path}/sites/{frappe.local.site}{template_file}"
        
        # 处理文件名模板（提前验证）
        default_filename = f'{template_name}_{doc.get("name", "unknown")}.pdf'
        filename = default_filename
        
        if export_file_name:
            try:
                filename = frappe.render_template(
                    export_file_name, 
                    {
                        'doc': doc, 
                        'template_name': template_name, 
                        'path': path
                    }
                ) + '.pdf'
                # 验证文件名是否有效
                if not filename or not isinstance(filename, str):
                    filename = default_filename
            except Exception as e:                
                error_msg = _("Export file name template error: {0}. Using default filename.").format(str(e))
                frappe.log_error("PDF Filename template error", error_msg)
                filename = default_filename
        
        # 创建临时目录
        temp_dir = os.path.join(os.path.expanduser('~'), '.temp_pdf_export')
        os.makedirs(temp_dir, exist_ok=True)
        
        try:
            file_type = os.path.splitext(path)[1].lower()
            source_file = os.path.join(temp_dir, f"temp{file_type}")
            
            # 生成源文件（Excel或Word）
            if file_type == '.docx':
                temp_file = process_docxtpl(path, doc)
                temp_file.save(source_file)
            else:
                # Excel文件
                temp_file = openpyxl.load_workbook(filename=path)
                for ws in temp_file.worksheets:
                    process_worksheet(ws, doc, language, bench_path)
                temp_file.save(source_file)
            
            # 使用LibreOffice转换为PDF
            pdf_file = os.path.join(temp_dir, 'output.pdf')
            
            # 使用soffice命令进行转换
            try:
                # 添加更多转换选项以提高兼容性
                cmd = [
                    'soffice',
                    '--headless',
                    '--norestore',
                    '--nofirststartwizard',
                    '--nologo',
                    '--convert-to', 'pdf:writer_pdf_Export',
                    '--outdir', temp_dir,
                    source_file
                ]
                
                process = subprocess.run(
                    cmd,
                    capture_output=True,
                    text=True,
                    timeout=300  # 设置更长的超时时间（5分钟）
                )
                
                # 检查转换结果
                if process.returncode != 0:
                    error_msg = f"PDF conversion failed. Exit code: {process.returncode}\nError output: {process.stderr}\nStandard output: {process.stdout}"
                    frappe.log_error(error_msg, "PDF Export Error")
                    
                    # 尝试提供更具体的错误信息
                    if "error" in process.stderr.lower() or "error" in process.stdout.lower():
                        user_msg = _("PDF conversion failed due to an internal error. Please try a different template or contact support.")
                    elif "timeout" in process.stderr.lower():
                        user_msg = _("PDF conversion timed out. The document may be too complex. Please try a simpler template.")
                    elif "not found" in process.stderr.lower():
                        user_msg = _("LibreOffice not found or not properly installed. Please check installation.")
                    else:
                        user_msg = _("PDF conversion failed. Please check the error log for details.")
                    
                    return {"success": False, "message": user_msg}
                    
            except subprocess.TimeoutExpired:
                return {"success": False, "message": _("PDF conversion took too long. The document may be too complex. Please try a simpler template or export as Excel/Word instead.")}
            
            # 获取生成的PDF文件名 - LibreOffice使用输入文件名但扩展名为.pdf
            base_name = os.path.splitext(os.path.basename(source_file))[0]
            pdf_file = os.path.join(temp_dir, f"{base_name}.pdf")
            
            # 如果找不到默认名称的PDF文件，尝试其他可能的名称
            if not os.path.exists(pdf_file):
                # LibreOffice有时使用"output.pdf"作为输出文件名
                alt_pdf_file = os.path.join(temp_dir, 'output.pdf')
                if os.path.exists(alt_pdf_file):
                    pdf_file = alt_pdf_file
                else:
                    # 列出目录中所有的PDF文件
                    pdf_files = [f for f in os.listdir(temp_dir) if f.lower().endswith('.pdf')]
                    if pdf_files:
                        pdf_file = os.path.join(temp_dir, pdf_files[0])
            
            if not os.path.exists(pdf_file):
                error_msg = f"PDF file not found in temporary directory: {temp_dir}\nContents: {os.listdir(temp_dir)}"
                frappe.log_error(error_msg, "PDF Export Error")
                
                # 备选方案：返回原始文件
                fallback_msg = _("PDF conversion failed. Returning the original file instead.")
                frappe.log_error(fallback_msg, "PDF Export Fallback")
                
                # 读取原始文件作为备选
                with open(source_file, "rb") as f:
                    file_content = f.read()
                
                # 根据文件类型返回不同的内容类型
                if file_type == '.docx':
                    content_type = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
                    fallback_filename = filename.replace('.pdf', '.docx')
                else:
                    content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    fallback_filename = filename.replace('.pdf', '.xlsx')
                
                return {
                    "success": True,
                    "file_content": file_content.hex(),
                    "file_name": fallback_filename,
                    "content_type": content_type,
                    "fallback": True,
                    "message": fallback_msg
                }
            else:
                # 读取生成的PDF文件
                with open(pdf_file, 'rb') as f:
                    pdf_content = f.read()
                
        finally:
            # 清理临时文件
            try:
                import shutil
                shutil.rmtree(temp_dir)
            except Exception as e:
                frappe.log_error(f"Cleanup error: {str(e)}", "PDF Export Warning")

        # 返回成功结果和文件数据
        return {
            "success": True,
            "file_content": pdf_content.hex(),  # 使用hex编码避免编码问题
            "file_name": filename,
            "content_type": "application/pdf"
        }
        
    except Exception as e:
        frappe.log_error("PDF Export template processing error", traceback)
        return {
            "success": False, 
            "message": _("PDF export failed due to template processing error. Please check the error log for details.")
        }

def is_libreoffice_installed():
    """检查系统是否安装了LibreOffice"""
    try:
        # 检查Windows系统
        if platform.system() == "Windows":
            # 检查常见安装路径
            paths = [
                os.path.join(os.environ.get('PROGRAMFILES', 'C:\\Program Files'), 'LibreOffice', 'program', 'soffice.exe'),
                os.path.join(os.environ.get('PROGRAMFILES(X86)', 'C:\\Program Files (x86)'), 'LibreOffice', 'program', 'soffice.exe')
            ]
            return any(os.path.exists(path) for path in paths)
        
        # 检查Linux/macOS系统
        result = subprocess.run(['which', 'soffice'], capture_output=True, text=True)
        return result.returncode == 0
    except Exception:
        return False

def install_libreoffice():
    """安装LibreOffice"""
    try:
        if is_libreoffice_installed():
            return True
            
        frappe.msgprint(_("LibreOffice not found, attempting to install... (This may take a few minutes)"))
        
        # 获取操作系统信息
        os_name = platform.system()
        env = os.environ.copy()
        env['DEBIAN_FRONTEND'] = 'noninteractive'
        env['NEEDRESTART_MODE'] = 'a'  # 自动处理服务重启        
        # 根据操作系统执行不同的安装命令
        if os_name == "Linux":
            # 检测Linux发行版
            if os.path.exists('/etc/debian_version'):
                # Debian/Ubuntu系统
                subprocess.run(['sudo', 'apt-get', 'update'], check=True, env=env)
                subprocess.run([
                    'sudo', 'NEEDRESTART_MODE=a', 'apt-get', 'install', '-y',
                    '-o', 'Dpkg::Options::=--force-confold',
                    '-o', 'Dpkg::Options::=--force-confdef',
                    'libreoffice'
                ], check=True, env=env)
            elif os.path.exists('/etc/redhat-release'):
                # CentOS/RHEL系统
                subprocess.run([
                    'sudo', 'NEEDRESTART_MODE=a', 'yum', 'install', '-y',
                    '-o', 'Dpkg::Options::=--force-confold',
                    '-o', 'Dpkg::Options::=--force-confdef',
                    'libreoffice'
                ], check=True, env=env)
            else:
                frappe.throw(_("Unsupported Linux distribution. Please install LibreOffice manually."))
        
        elif os_name == "Windows":
            # 下载并安装LibreOffice
            frappe.throw(_("Please install LibreOffice manually on Windows: https://www.libreoffice.org/download/download-libreoffice/"))
        
        elif os_name == "Darwin":  # macOS
            # 使用Homebrew安装
            subprocess.run(['brew', 'install', '--cask', 'libreoffice'], check=True)
        
        else:
            frappe.throw(_("Unsupported operating system. Please install LibreOffice manually."))
        
        # 验证安装
        if is_libreoffice_installed():
            frappe.msgprint(_("LibreOffice installed successfully!"))
            return True
        else:
            frappe.log_error("LibreOffice installation failed", "PDF Export Error")
            frappe.throw(_("LibreOffice installation failed. Please install it manually."))
            return False
    except subprocess.CalledProcessError as e:
        frappe.log_error(f"LibreOffice installation failed: {str(e)}", "PDF Export Error")
        frappe.throw(_("LibreOffice installation failed: {0}. Please install it manually.").format(str(e)))
    except Exception as e:
        frappe.log_error(f"Unexpected error during LibreOffice installation: {str(e)}", "PDF Export Error")
        frappe.throw(_("Unexpected error during LibreOffice installation: {0}. Please install it manually.").format(str(e)))


def apply_vertical_merges(ws):
    """
    Applies vertical merges to columns marked with 'merge_v:true' comment.
    This function should be called after all data processing for the worksheet is complete.
    Fixes the issue by first calculating the true max data row to set a safe boundary.
    """
    # frappe.logger().debug(f"Start apply_vertical_merges for sheet: {ws.title}")

    merge_columns = []
    # 查找配置注释的逻辑保持不变 (在头几行查找 'merge_v:true')
    for col_idx in range(1, ws.max_column + 1):
        for r_idx in range(1, min(ws.max_row + 1, 5)): 
            cell = ws.cell(row=r_idx, column=col_idx)
            if cell.comment:
                comment_text_stripped_lower = cell.comment.text.strip().lower()
                if comment_text_stripped_lower == "merge_v:true":
                    merge_columns.append(col_idx)
                    cell.comment = None 
                    break 

    if not merge_columns:
        # frappe.logger().debug("No columns marked for vertical merge.")
        return

    # ⭐ 核心修正 1: 确定真正的最后数据行 (True Max Data Row)
    true_max_data_row = 0
    # 从当前最大行向前遍历，找到第一个包含非空值的行
    # 注意：这里 range(start, stop, step) 的 stop 是不包含的，所以要到 0
    # ws.max_column 必须准确反映出工作表的最大列数
    for r_idx in range(ws.max_row, 0, -1):
        is_row_empty = True
        for c_idx in range(1, ws.max_column + 1):
            cell_value = ws.cell(row=r_idx, column=c_idx).value
            if cell_value is not None and cell_value != "": # 检查值是否为None或空字符串
                is_row_empty = False
                break
        
        if not is_row_empty:
            true_max_data_row = r_idx
            break
            
    if true_max_data_row == 0:
        # frappe.logger().debug("Worksheet is effectively empty or data is above config rows.")
        return
    # ⭐ 核心修正 1 结束

    for col_idx in merge_columns:
        start_merge_row = -1
        previous_value = None

        # ⭐ 核心修正 2: 循环边界设置为 true_max_data_row + 2
        # 只在实际数据范围内进行检查，并增加一个额外迭代来触发最终合并
        for row_idx in range(1, true_max_data_row + 2): 
            
            is_final_row_iteration = (row_idx > true_max_data_row)
            
            if not is_final_row_iteration:
                current_cell = ws.cell(row=row_idx, column=col_idx)
                current_value = current_cell.value
            else:
                # 在额外迭代中，将 current_value 设为 None，保证它与 previous_value 不同，从而触发合并
                current_value = None 

            if start_merge_row == -1:
                # 寻找合并块的起始行
                if not is_final_row_iteration:
                    start_merge_row = row_idx
                    previous_value = current_value
            elif current_value == previous_value and not is_final_row_iteration:
                # 值相同，继续向下查找
                pass
            else:
                # 块结束：值改变，或到达了循环的最后一步
                
                # 合并块的结束行是前一行
                end_merge_row = row_idx - 1 

                if end_merge_row > start_merge_row:
                    # 仅在合并范围 >= 2 行时执行合并
                    
                    # 1. 设置垂直对齐方式为居中
                    top_left_cell_for_merge = ws.cell(row=start_merge_row, column=col_idx)
                    current_alignment = copy(top_left_cell_for_merge.alignment) if top_left_cell_for_merge.alignment else Alignment()
                    current_alignment.vertical = "center"
                    top_left_cell_for_merge.alignment = current_alignment

                    # 2. 执行合并
                    ws.merge_cells(start_row=start_merge_row, start_column=col_idx,
                                    end_row=end_merge_row, end_column=col_idx)
                
                if is_final_row_iteration:
                    # 如果是最后一次迭代，则循环结束
                    break 
                else:
                    # 开始新的合并块
                    start_merge_row = row_idx
                    previous_value = current_value

    # frappe.logger().debug("End apply_vertical_merges")

def parse_formatter_instructions(ws, cell, value, from_col=1, end_col=None):
    """
    解析并执行Excel格式化指令
    
    Args:
        value: 包含格式化指令的字符串
        ws: Excel工作表对象
        cell: 当前单元格对象
        from_col: 起始列，默认为1
        end_col: 结束列，默认为None（使用最大列）
    """
    if end_col is None:
        end_col = ws.max_column
    
    # 清空指令单元格的内容
    cell.value = ''
    
    # 提取格式化指令部分
    if value.startswith("formatter:"):
        formatter_list = value[10:].split(";")
        
        # 用于存储merge_h的范围，供其他格式化指令使用
        merge_h_range = None
        
        for formatter in formatter_list:
            formatter = formatter.strip()
            if not formatter:
                continue
                
            # 处理水平合并 - 合并指定列范围，使用当前行
            if formatter.startswith("merge_h"):
                # 解析参数：merge_h(起始列, 结束列)
                match = re.match(r'merge_h\((\d+),\s*(\d+)\)', formatter)
                if match:
                    start_col_merge = int(match.group(1))
                    end_col_merge = int(match.group(2))
                    # 使用当前单元格的行，合并指定列范围
                    ws.merge_cells(start_row=cell.row, start_column=start_col_merge, 
                                 end_row=cell.row, end_column=end_col_merge)
                    print(f"合并单元格: 行{cell.row}, 列{start_col_merge}-{end_col_merge}")
                    
                    # 保存merge_h的范围，供其他格式化指令使用
                    merge_h_range = (start_col_merge, end_col_merge)
                
            # 处理垂直合并 - 合并指定行范围，使用当前列
            elif formatter.startswith("merge_v"):
                # 解析参数：merge_v(起始行, 结束行)
                match = re.match(r'merge_v\((\d+),\s*(\d+)\)', formatter)
                if match:
                    start_row_merge = int(match.group(1))
                    end_row_merge = int(match.group(2))
                    # 使用当前单元格的列，合并指定行范围
                    ws.merge_cells(start_row=start_row_merge, start_column=cell.column, 
                                 end_row=end_row_merge, end_column=cell.column)
                    print(f"合并单元格: 列{cell.column}, 行{start_row_merge}-{end_row_merge}")
            
            # 处理背景颜色
            elif formatter.startswith("background_color"):
                match = re.match(r'background_color\(([^)]+)\)', formatter)
                if match:
                    color_name = match.group(1).strip().lower()
                    color_map = {
                        'grey': 'CCCCCC',
                        'gray': 'CCCCCC',
                        'red': 'FF0000', 
                        'green': '00FF00',
                        'blue': '0000FF',
                        'yellow': 'FFFF00',
                        'orange': 'FFA500',
                        'purple': '800080',
                        'pink': 'FFC0CB',
                        'brown': 'A52A2A',
                        'black': '000000',
                        'white': 'FFFFFF'
                    }
                    
                    hex_color = color_map.get(color_name, 'CCCCCC')  # 默认灰色
                    fill = PatternFill(start_color=hex_color, end_color=hex_color, fill_type="solid")
                    
                    # 如果有merge_h范围，使用merge_h的范围；否则使用默认范围
                    if merge_h_range:
                        bg_start_col, bg_end_col = merge_h_range
                    else:
                        bg_start_col, bg_end_col = from_col, end_col
                    
                    # 应用到指定列范围
                    for col in range(bg_start_col, bg_end_col + 1):
                        cell_to_fill = ws.cell(row=cell.row, column=col)
                        cell_to_fill.fill = fill
                    print(f"背景颜色应用到: 行{cell.row}, 列{bg_start_col}-{bg_end_col}")

            # 处理字体样式
            elif formatter.startswith("font_"):
                # 如果有merge_h范围，使用merge_h的范围；否则使用默认范围
                if merge_h_range:
                    font_start_col, font_end_col = merge_h_range
                else:
                    font_start_col, font_end_col = from_col, end_col
                
                # 解析字体大小：font_size(数字)
                if formatter.startswith("font_size("):
                    match = re.match(r'font_size\((\d+)\)', formatter)
                    if match:
                        font_size = int(match.group(1))
                        for col in range(font_start_col, font_end_col + 1):
                            cell_to_format = ws.cell(row=cell.row, column=col)
                            if cell_to_format.font:
                                cell_to_format.font = Font(
                                    size=font_size,
                                    bold=cell_to_format.font.bold,
                                    italic=cell_to_format.font.italic,
                                    color=cell_to_format.font.color
                                )
                            else:
                                cell_to_format.font = Font(size=font_size)
                
                # 解析字体颜色：font_color(颜色名)
                elif formatter.startswith("font_color("):
                    match = re.match(r'font_color\(([^)]+)\)', formatter)
                    if match:
                        color_name = match.group(1).strip().lower()
                        color_map = {
                            'red': 'FF0000',
                            'green': '00FF00', 
                            'blue': '0000FF',
                            'yellow': 'FFFF00',
                            'black': '000000',
                            'white': 'FFFFFF',
                            'grey': '666666',
                            'gray': '666666'
                        }
                        font_color = color_map.get(color_name, '000000')  # 默认黑色
                        
                        for col in range(font_start_col, font_end_col + 1):
                            cell_to_format = ws.cell(row=cell.row, column=col)
                            if cell_to_format.font:
                                cell_to_format.font = Font(
                                    color=font_color,
                                    bold=cell_to_format.font.bold,
                                    italic=cell_to_format.font.italic,
                                    size=cell_to_format.font.size
                                )
                            else:
                                cell_to_format.font = Font(color=font_color)
                
                # 字体加粗
                elif formatter == "font_bold":
                    for col in range(font_start_col, font_end_col + 1):
                        cell_to_format = ws.cell(row=cell.row, column=col)
                        if cell_to_format.font:
                            cell_to_format.font = Font(
                                bold=True, 
                                italic=cell_to_format.font.italic,
                                size=cell_to_format.font.size,
                                color=cell_to_format.font.color
                            )
                        else:
                            cell_to_format.font = Font(bold=True)
                
                # 字体斜体
                elif formatter == "font_italic":
                    for col in range(font_start_col, font_end_col + 1):
                        cell_to_format = ws.cell(row=cell.row, column=col)
                        if cell_to_format.font:
                            cell_to_format.font = Font(
                                italic=True, 
                                bold=cell_to_format.font.bold,
                                size=cell_to_format.font.size,
                                color=cell_to_format.font.color
                            )
                        else:
                            cell_to_format.font = Font(italic=True)
                
                # 字体下划线
                elif formatter == "font_underline":
                    for col in range(font_start_col, font_end_col + 1):
                        cell_to_format = ws.cell(row=cell.row, column=col)
                        if cell_to_format.font:
                            cell_to_format.font = Font(
                                underline='single',
                                bold=cell_to_format.font.bold,
                                italic=cell_to_format.font.italic,
                                size=cell_to_format.font.size,
                                color=cell_to_format.font.color
                            )
                        else:
                            cell_to_format.font = Font(underline='single')
            
            # 处理对齐方式
            elif formatter.startswith("align_"):
                # 如果有merge_h范围，使用merge_h的范围；否则使用默认范围
                if merge_h_range:
                    align_start_col, align_end_col = merge_h_range
                else:
                    align_start_col, align_end_col = from_col, end_col
                
                alignment_map = {
                    "align_center": Alignment(horizontal='center', vertical='center'),
                    "align_left": Alignment(horizontal='left', vertical='center'),
                    "align_right": Alignment(horizontal='right', vertical='center'),
                    "align_top": Alignment(horizontal='center', vertical='top'),
                    "align_bottom": Alignment(horizontal='center', vertical='bottom')
                }
                
                if formatter in alignment_map:
                    for col in range(align_start_col, align_end_col + 1):
                        cell_to_align = ws.cell(row=cell.row, column=col)
                        cell_to_align.alignment = alignment_map[formatter]